﻿using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using NpgsqlTypes;
using System.Data;
using MikroObject;

namespace Aktarim
{
    public class Data
    {
        public string Server { get; set; }
        public int Port { get; set; }
        public string User { get; set; }
        public string Password { get; set; }
        public string Database { get; set; }

        public Data()
        {
            this.Server = Config.GetConfig.GetValue(CParams.PgServer, "127.0.0.1");
            this.Password = Config.GetConfig.GetValue(CParams.PgPassword, "KORDO");
            this.Port = int.Parse(Config.GetConfig.GetValue(CParams.PgPort, "5432"));
            this.User = Config.GetConfig.GetValue(CParams.PgUserId, "begum");
            this.Database = Config.GetConfig.GetValue(CParams.PgDatabase, "fastrest");
        }

        private string GetString()
        {
            return string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", this.Server, this.Port, this.User, this.Password, this.Database);
        }

        public DataTable List(string sqlText)
        {
            DataTable dt = new DataTable();
            using (NpgsqlConnection cn = new NpgsqlConnection(GetString()))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sqlText, cn))
                {
                    using (NpgsqlDataAdapter adp = new NpgsqlDataAdapter(cmd))
                    {
                        adp.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public object ExecScaler(string cmdText)
        {
            object sonuc = null;
            using (NpgsqlConnection cn = new NpgsqlConnection(GetString()))
            {
                cn.Open();
                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdText, cn))
                {
                    try
                    {
                        sonuc = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        Logging.GetLog.ErrorLog(ex);
                    }
                }
                cn.Close();
            }
            return sonuc;
        }

        public DataTable CariList()
        {
            return List("select id,adi,adres,adres2,tel1,tel2,fax,ilgili,email,cari_kod,vergid,vergin,firma from stok_group");
        }

        public DataTable AnaGrupList()
        {
            DataTable sana = List("select id,adi from stok_tip");
            DataTable pana = List("select id,adi from product_group");

            DataTable dt = new DataTable();
            dt.Columns.Add("kod", typeof(string));
            dt.Columns.Add("adi", typeof(string));

            for (int i = 0; i < sana.Rows.Count; i++)
                dt.Rows.Add("S." + sana.Rows[i][0].ToString(), sana.Rows[i][1].ToString());

            for (int i = 0; i < pana.Rows.Count; i++)
                dt.Rows.Add("U." + pana.Rows[i][0].ToString(), pana.Rows[i][1].ToString());

            return dt;
        }

        public DataTable AltGrupList()
        {
            DataTable salt = List("select id,adi,t_id from stok_altgroup");
            DataTable palt = List("select id,adi,ana_grup from product_alttip");


            DataTable dt = new DataTable();
            dt.Columns.Add("kod", typeof(string));
            dt.Columns.Add("adi", typeof(string));
            dt.Columns.Add("anakod", typeof(string));

            for (int i = 0; i < salt.Rows.Count; i++)
                dt.Rows.Add("S." + salt.Rows[i][0].ToString(), salt.Rows[i][1].ToString(), "S." + salt.Rows[i][2].ToString());

            for (int i = 0; i < palt.Rows.Count; i++)
                dt.Rows.Add("U." + palt.Rows[i][0].ToString(), palt.Rows[i][1].ToString(), "U." + palt.Rows[i][2].ToString());

            return dt;
        }

        public Dictionary<int, int> KdvList()
        {
            Dictionary<int, int> kdvler = new Dictionary<int, int>();
            DataTable dt = List("select id,oran from kdv");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                kdvler.Add(Convert.ToInt32(dt.Rows[i][0]), Convert.ToInt32(dt.Rows[i][1]));
            }
            return kdvler;
        }

        public DataTable KasaList()
        {
            return List("SELECT kasa, adi FROM kasa_info");
        }

        public decimal[] fiyatlar(int pluno)
        {
            decimal[] sonuc = new decimal[3];
            sonuc[0] = 0;
            sonuc[1] = 0;
            sonuc[2] = 0;
            DataTable dt = List(string.Format("SELECT fiyat, fiyat_2, fiyat_3 FROM         product_fiyat  WHERE     (plu = {0}) ORDER BY bittar DESC LIMIT 1", pluno));
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0][0] != DBNull.Value) sonuc[0] = Convert.ToDecimal(dt.Rows[0][0]); else sonuc[0] = 0;
                if (dt.Rows[0][1] != DBNull.Value) sonuc[1] = Convert.ToDecimal(dt.Rows[0][1]); else sonuc[1] = 0;
                if (dt.Rows[0][2] != DBNull.Value) sonuc[2] = Convert.ToDecimal(dt.Rows[0][2]); else sonuc[2] = 0;
            }
            return sonuc;
        }

        public DataTable StokList()
        {
            DataTable stock = List("SELECT     stok.id, stok.adi, stok.tip AS ana_grup, a_birim.birim AS birim1_ad, stok.birim_katsayi AS birim1_katsayi, a_birim_1.birim AS birim2_ad, stok.katsayi2 AS birim2_katsayi, stok.stok_id AS alt_grup, stok.grup AS teminci, kdv.oran AS kdv_orani,stok.st_code as muhkod FROM         stok INNER JOIN a_birim ON stok.birim1 = a_birim.id INNER JOIN a_birim AS a_birim_1 ON stok.birim3 = a_birim_1.id LEFT OUTER JOIN kdv ON stok.kdv = kdv.id");
            DataTable product = List("SELECT     product.id, product.product_name, product.tip AS ana_grup, a_birim.birim AS birim1_ad, kdv.oran AS kdv_orani, product.altalt_tip AS alt_grup,a_birim.katsayi AS birim1_katsayi,product.pr_code as muhkod FROM         product LEFT OUTER JOIN kdv ON product.kdv = kdv.id LEFT OUTER JOIN a_birim ON product.birim = a_birim.id WHERE     (product.isrecete =false)");

            DataTable dt = new DataTable();
            dt.Columns.Add("kod", typeof(string));
            dt.Columns.Add("adi", typeof(string));
            dt.Columns.Add("birim1_ad", typeof(string));
            dt.Columns.Add("birim1_katsayi", typeof(decimal));
            dt.Columns.Add("birim2_ad", typeof(string));
            dt.Columns.Add("birim2_katsayi", typeof(decimal));
            dt.Columns.Add("kdv_orani", typeof(int));
            dt.Columns.Add("ana_grup_kodu", typeof(string));
            dt.Columns.Add("alt_grup_kodu", typeof(string));
            dt.Columns.Add("teminci", typeof(string));
            dt.Columns.Add("muhasebekodu", typeof(string));
            dt.Columns.Add("fiyat1", typeof(decimal));
            dt.Columns.Add("fiyat2", typeof(decimal));
            dt.Columns.Add("fiyat3", typeof(decimal));


            string stokkod = string.Empty;
            string stokad = string.Empty;
            string birim1adi = string.Empty;
            decimal birim1sayi = 0;
            string birim2adi = string.Empty;
            decimal birim2sayi = 0;
            int kdv = 0;
            string anagrup = string.Empty;
            string altgrup = string.Empty;
            string teminci = string.Empty;
            string muhkod = string.Empty;

            for (int i = 0; i < stock.Rows.Count; i++)
            {
                stokkod = "S." + stock.Rows[i]["id"].ToString();
                stokad = stock.Rows[i]["adi"].ToString();
                birim1adi = stock.Rows[i]["birim1_ad"].ToString();
                birim2adi = stock.Rows[i]["birim2_ad"].ToString();
                muhkod = stock.Rows[i]["muhkod"].ToString();

                if (stock.Rows[i]["birim1_katsayi"] != DBNull.Value) birim1sayi = Convert.ToDecimal(stock.Rows[i]["birim1_katsayi"]); else birim1sayi = 0;
                if (stock.Rows[i]["birim2_katsayi"] != DBNull.Value) birim2sayi = Convert.ToDecimal(stock.Rows[i]["birim2_katsayi"]); else birim2sayi = 0;
                if (stock.Rows[i]["kdv_orani"] != DBNull.Value) kdv = Convert.ToInt32(stock.Rows[i]["kdv_orani"]); else kdv = 0;
                if (!string.IsNullOrEmpty(stock.Rows[i]["ana_grup"].ToString())) anagrup = "S." + stock.Rows[i]["ana_grup"].ToString(); else anagrup = string.Empty;
                if (!string.IsNullOrEmpty(stock.Rows[i]["alt_grup"].ToString())) altgrup = "S." + stock.Rows[i]["alt_grup"].ToString(); else altgrup = string.Empty;
                teminci = stock.Rows[i]["teminci"].ToString();

                dt.Rows.Add(stokkod, stokad, birim1adi, birim1sayi, birim2adi, birim2sayi, kdv, anagrup, altgrup, teminci, muhkod, 0, 0, 0);
            }

            for (int i = 0; i < product.Rows.Count; i++)
            {
                stokkod = "U." + product.Rows[i]["id"].ToString();
                stokad = product.Rows[i]["product_name"].ToString();
                birim1adi = product.Rows[i]["birim1_ad"].ToString();
                birim2adi = "";
                muhkod = product.Rows[i]["muhkod"].ToString();
                if (product.Rows[i]["birim1_katsayi"] != DBNull.Value) birim1sayi = Convert.ToDecimal(product.Rows[i]["birim1_katsayi"]); else birim1sayi = 0; ;
                birim2sayi = 0;
                if (product.Rows[i]["kdv_orani"] != DBNull.Value) kdv = Convert.ToInt32(product.Rows[i]["kdv_orani"]); else kdv = 0;
                if (!string.IsNullOrEmpty(product.Rows[i]["ana_grup"].ToString())) anagrup = "U." + product.Rows[i]["ana_grup"].ToString(); else anagrup = string.Empty;
                if (!string.IsNullOrEmpty(product.Rows[i]["alt_grup"].ToString())) altgrup = "U." + product.Rows[i]["alt_grup"].ToString(); else altgrup = string.Empty;
                teminci = "";

                decimal[] fiyat = fiyatlar(Convert.ToInt32(product.Rows[i]["id"]));

                dt.Rows.Add(stokkod, stokad, birim1adi, birim1sayi, birim2adi, birim2sayi, kdv, anagrup, altgrup, teminci, muhkod, fiyat[0], fiyat[1], fiyat[2]);
            }


            return dt;
        }

        public DataTable DepoList()
        {
            return List("select id,adi from stok_yer");
        }

        public List<SevkEvrak> SevkList(DateTime lastupdate)
        {
            DataTable evraklar = List(string.Format("SELECT     tarih, from_depo, to_depo, evrakno FROM depo_giris_cikis WHERE (sondegtarih >= '{0}') GROUP BY tarih, from_depo, to_depo, evrakno", ExtensionMethods.DateToString(lastupdate)));

            List<SevkEvrak> liste = new List<SevkEvrak>();
            SevkEvrak evr = null;

            for (int i = 0; i < evraklar.Rows.Count; i++)
            {
                evr = new SevkEvrak();
                evr.EvrakNo = evraklar.Rows[i]["evrakno"].ToString();
                evr.HedefDepo = Convert.ToInt32(evraklar.Rows[i]["to_depo"]);
                evr.KaynakDepo = Convert.ToInt32(evraklar.Rows[i]["from_depo"]);
                evr.Tarih = Convert.ToDateTime(evraklar.Rows[i]["tarih"]);

                DataTable dt = List(string.Format("SELECT     depo_giris_cikis.s_id, depo_giris_cikis.miktar, depo_giris_cikis.birim_fiyat, depo_giris_cikis.isstok, depo_giris_cikis.sirano, kdv.oran FROM depo_giris_cikis INNER JOIN stok ON depo_giris_cikis.s_id = stok.id INNER JOIN kdv ON stok.kdv = kdv.id WHERE     (depo_giris_cikis.tarih = '{0}') AND (depo_giris_cikis.from_depo = {1}) AND (depo_giris_cikis.to_depo = {2}) AND (depo_giris_cikis.evrakno = '{3}')", evr.Tarih, evr.KaynakDepo, evr.HedefDepo, evr.EvrakNo));

                for (int h = 0; h < dt.Rows.Count; h++)
                {
                    evr.Satirlar.Add(new EvrakSatir()
                    {
                        Id = Convert.ToInt32(dt.Rows[h]["sirano"]),
                        Fiyat = Convert.ToDecimal(dt.Rows[h]["birim_fiyat"]),
                        isStok = Convert.ToBoolean(dt.Rows[h]["isstok"]),
                        Miktar = Convert.ToDecimal(dt.Rows[h]["miktar"]),
                        StokId = Convert.ToInt32(dt.Rows[h]["s_id"]),
                        KdvOrani = Convert.ToInt32(dt.Rows[h]["oran"])
                    });
                }
                liste.Add(evr);
            }
            return liste;
        }

        private int FatDetayId(string irs_fatno)
        {
            object sonuc = ExecScaler(string.Format("SELECT stok_giris_cikis.sirano FROM stok_giris_cikis INNER JOIN fat_detay ON stok_giris_cikis.tarih = fat_detay.tarih AND stok_giris_cikis.s_group = fat_detay.s_group AND stok_giris_cikis.stok_yer = fat_detay.stok_yer AND stok_giris_cikis.evrakno = fat_detay.evrakno WHERE (fat_detay.irs_fatno = '{0}') AND (fat_detay.fat_turu = 0) limit 1", irs_fatno));
            if (sonuc != null)
                return Convert.ToInt32(sonuc);
            else
                return -1;
        }

        public List<Evrak> AlisList(DateTime lastupdate)
        {
            DataTable evraklar = List(string.Format("SELECT fat_detay.tarih, fat_detay.s_group AS cari, fat_detay.stok_yer AS depo, fat_detay.fat_turu AS evrak_tipi, fat_detay.evrakno AS belge_no FROM fat_detay INNER JOIN user_table_change ON fat_detay.user_change_id = user_table_change.sirano WHERE ((CASE WHEN user_table_change.edit_datetime IS NULL THEN user_table_change.insert_datetime ELSE user_table_change.edit_datetime END) >= '{0}') AND (user_table_change.tablo_no = 1) AND (fat_detay.neden = 1)", ExtensionMethods.DateToString(lastupdate)));

            List<Evrak> liste = new List<Evrak>();
            Evrak evr = null;

            for (int i = 0; i < evraklar.Rows.Count; i++)
            {
                evr = new Evrak();
                evr.EvrakNo = evraklar.Rows[i]["belge_no"].ToString();
                evr.Depo = Convert.ToInt32(evraklar.Rows[i]["depo"]);
                evr.CariKod = evraklar.Rows[i]["cari"].ToString();
                evr.Tarih = Convert.ToDateTime(evraklar.Rows[i]["tarih"]);
                evr.EvrakTip = (Evrak.AlisTip)Convert.ToInt32(evraklar.Rows[i]["evrak_tipi"]);
                if (evraklar.Rows[i]["belge_no"] != DBNull.Value) evr.IrsaliyeRecNo = FatDetayId(evraklar.Rows[i]["belge_no"].ToString()); else evr.IrsaliyeRecNo = -1;

                DataTable dt = List(string.Format("SELECT     sirano AS id, s_id AS stok_id, miktar1 AS miktar, birim_fiyat AS fiyat, vergi AS kdvtutar, iskontot AS isk1tutar, iskonto2t AS isk2tutar, iskonto3t AS isk3tutar,kdv AS kdv_oran,tutar FROM stok_giris_cikis WHERE (tarih =  '{0}') AND (evrakno = '{1}') AND (s_group = {2}) AND (stok_yer = {3}) AND (neden = 1)", ExtensionMethods.DateToString2(evr.Tarih), evr.EvrakNo, evr.CariKod, evr.Depo));

                for (int h = 0; h < dt.Rows.Count; h++)
                {
                    evr.Satirlar.Add(new EvrakSatir()
                    {
                        Id = Convert.ToInt32(dt.Rows[h]["id"]),
                        StokId = Convert.ToInt32(dt.Rows[h]["stok_id"]),
                        Miktar = Convert.ToDecimal(dt.Rows[h]["miktar"]),
                        Fiyat = Convert.ToDecimal(dt.Rows[h]["fiyat"]),
                        Tutar = Convert.ToDecimal(dt.Rows[h]["tutar"]),
                        KdvTutar = Convert.ToDecimal(dt.Rows[h]["kdvtutar"]),
                        Isk1Tutar = Convert.ToDecimal(dt.Rows[h]["isk1tutar"]),
                        Isk2Tutar = Convert.ToDecimal(dt.Rows[h]["isk2tutar"]),
                        Isk3Tutar = Convert.ToDecimal(dt.Rows[h]["isk3tutar"]),
                        KdvOrani = Convert.ToInt32(dt.Rows[h]["kdv_oran"])
                    });
                }
                liste.Add(evr);
            }
            return liste;
        }

        public List<Evrak> SatisList(DateTime lastupdate)
        {
            DataTable evraklar = List(string.Format("SELECT    kasa_raporu.id,kasa_raporu.kasa, kasa_info.depo_yer AS depo, kasa_raporu.id,kasa_raporu.tarih FROM  kasa_raporu INNER JOIN kasa_info ON kasa_raporu.kasa = kasa_info.kasa WHERE     (kasa_raporu.tarih = '{0}')", ExtensionMethods.DateToString(lastupdate)));

            List<Evrak> liste = new List<Evrak>();
            Evrak evr = null;
            decimal tutar = 0;
            decimal kdvTutar = 0;

            for (int i = 0; i < evraklar.Rows.Count; i++)
            {
                evr = new Evrak();
                evr.EvrakNo = "";
                evr.Depo = Convert.ToInt32(evraklar.Rows[i]["depo"]);
                evr.CariKod = "K" + evraklar.Rows[i]["kasa"].ToString();
                evr.Tarih = Convert.ToDateTime(evraklar.Rows[i]["tarih"]);
                evr.EvrakTip = Evrak.AlisTip.IrsaliyeliFatura;
                evr.IrsaliyeRecNo = -1;
                evr.KasaNo = Convert.ToInt32(evraklar.Rows[i]["kasa"]);

                DataTable dt = List(string.Format("SELECT     sirano as id,p_id AS stok_id, number,canceled, bfiyat, tutar,ctutar,iskonto, kdv_oran FROM product_mix WHERE     (k_id = {0})", evraklar.Rows[i]["id"].ToString()));

                for (int h = 0; h < dt.Rows.Count; h++)
                {
                    tutar = ExtensionMethods.Cevir(dt.Rows[h]["tutar"]) - ExtensionMethods.Cevir(dt.Rows[h]["ctutar"]);
                    kdvTutar = Convert.ToDecimal(dt.Rows[h]["bfiyat"]) * Convert.ToDecimal(dt.Rows[h]["kdv_oran"]) / (Convert.ToDecimal(100) + Convert.ToDecimal(dt.Rows[h]["kdv_oran"]));

                    evr.Satirlar.Add(new EvrakSatir()
                    {
                        Id = Convert.ToInt32(dt.Rows[h]["id"]),
                        StokId = Convert.ToInt32(dt.Rows[h]["stok_id"]),
                        Miktar = ExtensionMethods.Cevir(dt.Rows[h]["number"]) - ExtensionMethods.Cevir(dt.Rows[h]["canceled"]),
                        Fiyat = Convert.ToDecimal(dt.Rows[h]["bfiyat"]) - kdvTutar,
                        Tutar = tutar,
                        KdvTutar = kdvTutar,
                        Isk1Tutar = 0,
                        Isk2Tutar = 0,
                        Isk3Tutar = 0,
                        KdvOrani = Convert.ToInt32(dt.Rows[h]["kdv_oran"])
                    });
                }
                liste.Add(evr);
            }
            return liste;
        }
    }
}
